package com.weixin1.dao;

import com.weixin1.bean.Users;
import com.weixin1.utils.C3p0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class UserDao {
    private QueryRunner qr = new QueryRunner(C3p0Utils.comboPooledDataSource);

    public List<Users> list() throws SQLException {
        return qr.query("select u_id uId,u_name uName,u_message uMessage, u_gender uGender," +
                "u_age uAge,u_birthday uBirthday,u_email uEmail,u_time uTime from users", new BeanListHandler<>(Users.class) );
    }

    public Users getById(Long id) throws SQLException {
        return qr.query("select u_id uId,u_name uName,u_message uMessage, u_gender uGender," +
                "u_age uAge,u_birthday uBirthday,u_email uEmail,u_time uTime from users where u_id=?", new BeanHandler<>(Users.class), id);
    }

    public int save(Users user) throws SQLException {
        return qr.update("insert into users(u_name,u_message,u_gender," +
                "u_age,u_birthday,u_email,u_time) values(?,?,?,?,?,?,now())",user.getUName(),
                user.getUMessage(),user.getUGender(),user.getUAge(),user.getUBirthday(),user.getUEmail());
    }

    public int removeById(Long id) throws SQLException {
        return qr.update("delete from users where u_id=?", id);
    }

    public int update(Users user) throws SQLException {
        return qr.update("update users set u_name=?,u_gender=?,u_age=?,u_birthday=?,u_email=?,u_message=? where u_id=?",
                user.getUName(),user.getUGender(),user.getUAge(),user.getUBirthday(),user.getUEmail(),user.getUMessage(),user.getUId());
    }
    //查询用户个数
    public long unamecount() throws SQLException {
        Object n = qr.query("select count(u_name) n from users", new ScalarHandler<>("n"));
        long name=(long) n;
        return name;
    }
    //查询留言个数
    public long umessagecount() throws SQLException {
        Object m = qr.query("select count(u_message) m from users", new ScalarHandler<>("m"));
        long message=(long) m;
        return message;
    }
    //查询预约个数
    public long utime() throws SQLException {
        Object d = qr.query("select count(u_time) d from users", new ScalarHandler<>("d"));
        long utime=(long)d;
        return utime;
    }

    //获取总数据条数
    public int gettotal() throws SQLException {
        return qr.query("select count(*) from users",new ScalarHandler<Long>()).intValue();
    }
    /**
     *  分页查询其中包含 搜索 排序 功能
     * @param page 第几页
     * @param limit 一页几行
     * @return
     * @throws SQLException
     */
    public List<Users> selectBypage(int page,int limit) throws SQLException {
        return qr.query("select u_id uId,u_name uName,u_message uMessage, u_gender uGender," +
                        "u_age uAge,u_birthday uBirthday,u_email uEmail,u_time uTime from users limit ?,?",
                new BeanListHandler<>(Users.class),(page-1)*limit,limit);
    }

    public List<Users> selectContent() throws SQLException {
        return qr.query("select u_name uName,u_message uMessage,u_time uTime from users",new BeanListHandler<>(Users.class));
    }

}
